﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.IO;
using System.Linq;
using System.Text;

namespace LittleBee.DBA
{
	public class MySqlAccess : DBAccess
	{
		//private MySqlAccess()
		//	: base()
		//{ }

		public MySqlAccess(string strConnection)
			: base(strConnection)
		{
			if (string.IsNullOrEmpty(strConnection))
			{
				throw new ArgumentException($"Database connection string is set to be null or empty.");
			}
		}
		public override string GetConnectionString(params object[] aoParams)
		{
			string strConn = $"server={_dctConfig["server"]};User ID={_dctConfig["User ID"]};password={_dctConfig["password"]};";

			foreach (string strParam in aoParams)
			{
				string strValue = _dctConfig[strParam];
				string strTmp = $"{strParam}={strValue};";
				strConn += strTmp;
			}

			return strConn;
		}

		protected override DBConnectionPool CreateDBConnectionPool(string strConnection, uint nCapcity = 100)
		{
			return new MySqlConnectionPool(strConnection, nCapcity) as DBConnectionPool;
		}

		public override bool CheckDbExist(string strDBName = "")
		{
			bool blRet = false;

			string strConn = GetConnectionString();

			if (string.IsNullOrEmpty(strDBName))
				strDBName = _dctConfig["Database"];

			try
			{
				using (var conn = new MySqlConnection(strConn))
				{
					conn.Open();
					MySqlCommand dbc = conn.CreateCommand();

					dbc.CommandText = $"select count(*) from information_schema.SCHEMATA where SCHEMA_NAME = '{strDBName}'";

					if ((Int64)dbc.ExecuteScalar() == 1)
					{
						blRet = true;
					}
				}
			}
			catch (Exception ex)
			{
				Console.WriteLine($"Exception: {ex.Message}\n{ex.StackTrace}");
			}


			return blRet;
		}

		public override bool CreateDatabase(string strDBName)
		{
			bool blRet = false;
			string strConn = GetConnectionString();

			if (string.IsNullOrEmpty(strDBName))
				strDBName = _dctConfig["Database"];

			try
			{
				using (var conn = new MySqlConnection(strConn))
				{
					conn.Open();

					DbCommand dbc = conn.CreateCommand();
					dbc.CommandText = $"CREATE DATABASE {strDBName};";

					dbc.ExecuteNonQuery();

					blRet = true;
				}
			}
			catch (MySqlException ex)
			{
				if (ex.Number == 1007)
					blRet = true;
			}
			catch (Exception ex)
			{
				Console.WriteLine($"Exception: {ex.Message}\n{ex.StackTrace}");
			}

			return blRet;
		}

		public override bool CheckTableExist(string tableName)
		{
			bool blRet = false;

			string strConn = GetConnectionString("Database");

			try
			{
				using (var conn = new MySqlConnection(strConn))
				{
					conn.Open();

					//string dbName = _dctConfig["Database"];

					tableName = tableName.ToLower();
					blRet = conn.GetSchema("Tables").AsEnumerable().Any(x => x.Field<string>("TABLE_NAME") == tableName);
					//DbCommand dbc = conn.CreateCommand();
					//dbc.CommandText = $"SELECT `TABLE_NAME` FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_SCHEMA`='{dbName}' AND `TABLE_NAME`='{tableName}';";

					//if ((Int64)dbc.ExecuteScalar() == 1)
					//{
					//	blRet = true;
					//}
				}
			}
			catch (Exception ex)
			{
				Console.WriteLine($"Exception: {ex.Message}\n{ex.StackTrace}");
			}

			return blRet;
		}

		/// <summary>
		/// TODO:: 
		/// </summary>
		/// <param name="tableName"></param>
		/// <returns></returns>
		public override bool DuplicateTable(string tableName)
		{
			bool blRet = false;

			/*string strConn = $"server={_dctConfig["server"]};User ID={_dctConfig["User ID"]};password={_dctConfig["password"]};Database={_dctConfig["Database"]};";
			DbConnection conn = null;

			try
			{
				conn = new MySqlConnection(strConn);
				conn.Open();

				DbCommand dbc = conn.CreateCommand();
				dbc.CommandText = $"select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='dbname' and TABLE_NAME='tablename' ;";

				if ((Int64)dbc.ExecuteScalar() == 1)
				{
					blRet = true;
				}
			}
			finally
			{
				conn.Close();
			}*/

			return blRet;
		}

		public override bool ExecuteScriptFile(string strScriptFile)
		{
			string strConn = GetConnectionString("Database");

			bool blRet;
			using (var conn = new MySqlConnection(strConn))
			{
				conn.Open();
				MySqlScript script = new MySqlScript(conn);

				string sql = File.ReadAllText(strScriptFile, Encoding.UTF8);
				script.Query = sql;

				blRet = script.Execute() > 0;
			}

			return blRet;
		}

		/// <summary>
		/// 需要获得多个结果集的时候用该方法，返回DataSet对象。
		/// </summary>
		/// <param name="sql语句"></param>
		/// <returns></returns>
		public override DataSet ExecuteDataSet(string sql, params MySqlParameter[] aoParams)
		{
			MySqlConnection? conn = GetConnection() as MySqlConnection;

			DataSet ds = new DataSet();

			if (conn != null)
			{
				MySqlDataAdapter sqlda = new MySqlDataAdapter(sql, conn as MySqlConnection);
				sqlda.SelectCommand.Parameters.AddRange(aoParams);
				sqlda.Fill(ds);

				PutConnection(conn);
			}

			return ds;
		}

		/// <summary>
		/// 获得单个结果集时使用该方法，返回DataTable对象。
		/// </summary>
		/// <param name="sql"></param>
		/// <returns></returns>

		public override DataTable ExecuteDataTable(string sql, params MySqlParameter[] paras)
		{
			DataTable dt = new();
			if (GetConnection() is MySqlConnection conn)
			{
				MySqlDataAdapter sqlda = new MySqlDataAdapter(sql, conn);
				sqlda.SelectCommand.Parameters.AddRange(paras);
				sqlda.Fill(dt);

				PutConnection(conn);
			}
			return dt;
		}


		/// <summary>   
		/// 执行一条计算查询结果语句，返回查询结果（object）。   
		/// </summary>   
		/// <param name="SQLString">计算查询结果语句</param>   
		/// <returns>查询结果（object）</returns>   
		public override object ExecuteScalar(string SQLString, params MySqlParameter[] paras)
		{
			object obj = System.DBNull.Value;

			if (GetConnection() is MySqlConnection conn)
			{
				using (MySqlCommand cmd = new MySqlCommand(SQLString, conn))
				{
					try
					{
						cmd.Parameters.AddRange(paras);
						obj = cmd.ExecuteScalar();
					}
					catch (MySql.Data.MySqlClient.MySqlException e)
					{
						throw e;
					}
				}

				PutConnection(conn);
			}

			return obj;
		}

		/// <summary>
		/// 执行Update,Delete,Insert操作
		/// </summary>
		/// <param name="sql"></param>
		/// <returns></returns>
		public override int ExecuteNonquery(string sql, params MySqlParameter[] paras)
		{
			int nRet = 0;

			if (GetConnection() is MySqlConnection conn)
			{
				using (MySqlCommand cmd = new(sql, conn))
				{
					cmd.Parameters.AddRange(paras);
					nRet = cmd.ExecuteNonQuery();
				}
				PutConnection(conn);
			}
			return nRet;
		}

		/// <summary>
		/// 调用存储过程 无返回值
		/// </summary>
		/// <param name="procname">存储过程名</param>
		/// <param name="paras">sql语句中的参数数组</param>
		/// <returns></returns>
		public override int ExecuteProcNonQuery(string procname, params MySqlParameter[] paras)
		{
			int nRet = 0;

			if (GetConnection() is MySqlConnection conn)
			{
				using (MySqlCommand cmd = new(procname, conn))
				{
					cmd.CommandType = CommandType.StoredProcedure;
					cmd.Parameters.AddRange(paras);
					nRet = cmd.ExecuteNonQuery();
				}
				PutConnection(conn);
			}

			return nRet;
		}

		/// <summary>
		/// 存储过程 返回Datatable
		/// </summary>
		/// <param name="procname"></param>
		/// <param name="paras"></param>
		/// <returns></returns>
		public override DataTable ExecuteProcQuery(string procname, params MySqlParameter[] paras)
		{
			DataTable dt = new DataTable();

			if (GetConnection() is MySqlConnection conn)
			{
				using (MySqlCommand cmd = new MySqlCommand(procname, conn))
				{
					cmd.CommandType = CommandType.StoredProcedure;
					cmd.Parameters.AddRange(paras);
					MySqlDataAdapter sqlda = new MySqlDataAdapter(cmd);
					sqlda.Fill(dt);
				}
				PutConnection(conn);
			}
			return dt;
		}

		/// <summary>
		/// 多语句的事务管理
		/// </summary>
		/// <param name="cmds">命令数组</param>
		/// <returns></returns>
		public override bool ExcuteCommandByTran(params string[] cmds)
		{
			if (GetConnection() is MySqlConnection conn)
			{
				MySqlCommand cmd = new MySqlCommand
				{
					Connection = conn
				};

				MySqlTransaction tran = conn.BeginTransaction();
				cmd.Transaction = tran;

				foreach (string item in cmds)
				{
					string strCmd = item.Trim();
					if (!string.IsNullOrEmpty(strCmd))
					{
						cmd.CommandText = strCmd;
						cmd.ExecuteNonQuery();
					}
				}
				tran.Commit();

				PutConnection(conn);
			}
			return true;
		}

		/// <summary>
		/// 分批提交的多语句的事务管理
		/// </summary>
		/// <param name="cmds">命令数组</param>
		/// <returns></returns>
		public override bool ExcuteCommandByTran(int nCmdsPerTran, params string[] cmds)
		{
			if (nCmdsPerTran <= 1)
				return ExcuteCommandByTran(cmds);

			if (GetConnection() is MySqlConnection conn)
			{
				MySqlCommand cmd = new MySqlCommand
				{
					Connection = conn
				};

				MySqlTransaction tran = conn.BeginTransaction();
				cmd.Transaction = tran;

				int n = 0;
				foreach (string item in cmds)
				{
					string strCmd = item.Trim();
					if (!string.IsNullOrEmpty(strCmd))
					{
						cmd.CommandText = strCmd;
						cmd.ExecuteNonQuery();
					}

					++n;
					if (n >= nCmdsPerTran)
					{
						tran.Commit();
						tran = conn.BeginTransaction();
						n = 0;
					}
				}
				//tran.Commit();

				PutConnection(conn);
			}

			return true;
		}

		///分页
		public override DataTable ExcuteDataWithPage(string sql, ref int totalCount, params MySqlParameter[] paras)
		{
			DataTable dt = new DataTable();

			if (GetConnection() is MySqlConnection conn)
			{
				MySqlDataAdapter dap = new MySqlDataAdapter(sql, conn as MySqlConnection);
				dap.SelectCommand.Parameters.AddRange(paras);
				dap.Fill(dt);
				MySqlParameter ttc = dap.SelectCommand.Parameters["@totalCount"];
				if (ttc != null)
				{
					totalCount = Convert.ToInt32(ttc.Value);
				}

				PutConnection(conn);
			}
			return dt;
		}
	}
}
